package org.fiel.admin.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.fiel.admin.struts.forms.UserForm;
import org.fiel.domain.User;

public class UserDAO
{
    private static Logger logger = Logger.getLogger(UserDAO.class);

    public UserDAO() throws Exception
    {
        conn = null;
        try
        {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/FielDB");
            conn = ds.getConnection();
        }
        catch (Exception e)
        {
            System.err.println("UserDAO() Error:\n" + e.getMessage());
            throw new Exception("UserDAO() Error:\n" + e.getMessage());
        }
    }

    public void createUser(UserForm user) throws Exception
    {
        PreparedStatement pstmt = null;
        try
        {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("INSERT INTO usuarios (Empresa,Userid,Userpass,Nivel,Contacto,FechaVen) VALUES(?,?,?,?,?,?)");
            pstmt.setString(1, user.getEmpresa());
            pstmt.setString(2, user.getUserid());
            pstmt.setString(3, user.getUserpass());
            pstmt.setString(4, user.getNivel());
            pstmt.setString(5, user.getContacto());

            if (user.getFechaVen() == null || user.getFechaVen().trim().length() == 0)
            {
                logger.debug("Agregando la fecha nula.");
                pstmt.setString(6, null);
            }
            else
            {
                logger.debug("Agregando la fecha: "+ user.getFechaVen() +".");
                pstmt.setString(6, user.getFechaVen());
            }
            
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
        }
        catch (Exception exception)
        {
            conn.rollback();
            logger.error("ERROR", exception);
            
            throw new Exception("Error al agregar el usuario. Contacte a soporte técnico.");
        }
        finally
        {
            try
            {
                if (pstmt != null)
                {
                    pstmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception exception)
            {
                logger.error("ERROR", exception);
            }
        }
    }

    public List retrieveUsers() throws Exception
    {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList users = new ArrayList();
        try
        {
            pstmt = conn.prepareStatement("SELECT * FROM usuarios ORDER BY Empresa,Userid");
            User user;
            for (rset = pstmt.executeQuery(); rset.next(); users.add(user))
            {
                user = new User();
                user.setEmpresa(rset.getString("Empresa"));
                user.setId(rset.getString("Userid"));
                user.setPassword(rset.getString("Userpass"));
                user.setNivel(rset.getString("Nivel"));
                user.setContacto(rset.getString("Contacto"));
                user.setFechaven(rset.getDate("FechaVen"));
                user.setIdentint(Integer.valueOf(rset.getString("Idenint")));
            }
        }
        catch (Exception e)
        {
            System.err.println("UserDAO.retrieveUsers() Error:\n" + e.getMessage());
            throw new Exception("UserDAO.retrieveUsers() Error:\n" + e.getMessage());
        }
        finally
        {
            try
            {
                if (rset != null)
                {
                    rset.close();
                }
                if (pstmt != null)
                {
                    pstmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception ex)
            {
            }
        }
        return users;
    }

    public List retrieveUsersByEmpresa(String empresa) throws Exception
    {
        Statement stmt = null;
        ResultSet rset = null;
        ArrayList users = new ArrayList();
        try
        {
            stmt = conn.createStatement();
            User user;
            for (rset = stmt.executeQuery("SELECT * FROM usuarios WHERE empresa LIKE \"%" + empresa + "%\" ORDER BY Empresa,Userid"); rset.next(); users.add(user))
            {
                user = new User();
                user.setEmpresa(rset.getString("Empresa"));
                user.setId(rset.getString("Userid"));
                user.setPassword(rset.getString("Userpass"));
                user.setNivel(rset.getString("Nivel"));
                user.setContacto(rset.getString("Contacto"));
                user.setFechaven(rset.getDate("FechaVen"));
                user.setIdentint(Integer.valueOf(rset.getString("Idenint")));
            }
        }
        catch (Exception e)
        {
            System.err.println("UserDAO.retrieveUsersByEmpresa() Error:\n" + e.getMessage());
            throw new Exception("UserDAO.retrieveUsersByEmpresa() Error:\n" + e.getMessage());
        }
        finally
        {
            try
            {
                if (rset != null)
                {
                    rset.close();
                }
                if (stmt != null)
                {
                    stmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception ex)
            {
            }
        }
        return users;
    }

    public List retrieveUsersByFechaVen() throws Exception
    {
        Statement stmt = null;
        ResultSet rset = null;
        ArrayList users = new ArrayList();
        try
        {
            stmt = conn.createStatement();
            User user;
            for (rset = stmt.executeQuery("SELECT * FROM usuarios WHERE FechaVen > '0000-00-00' AND FechaVen <= sysdate() ORDER BY Empresa,Userid"); rset.next(); users.add(user))
            {
                user = new User();
                user.setEmpresa(rset.getString("Empresa"));
                user.setId(rset.getString("Userid"));
                user.setPassword(rset.getString("Userpass"));
                user.setNivel(rset.getString("Nivel"));
                user.setContacto(rset.getString("Contacto"));
                user.setFechaven(rset.getDate("FechaVen"));
                user.setIdentint(Integer.valueOf(rset.getString("Idenint")));
            }
        }
        catch (Exception e)
        {
            System.err.println("UserDAO.retrieveUsersByFechaVen() Error:\n" + e.getMessage());
            throw new Exception("UserDAO.retrieveUsersByFechaVen() Error:\n" + e.getMessage());
        }
        finally
        {
            try
            {
                if (rset != null)
                {
                    rset.close();
                }
                if (stmt != null)
                {
                    stmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception ex)
            {
            }
        }
        return users;
    }

    public User retrieveUser(String id) throws Exception
    {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        User user = null;
        try
        {
            pstmt = conn.prepareStatement("SELECT * FROM usuarios WHERE Idenint = ?");
            pstmt.setString(1, id);
            rset = pstmt.executeQuery();
            if (rset.next())
            {
                user = new User();
                user.setEmpresa(rset.getString("Empresa"));
                user.setId(rset.getString("Userid"));
                user.setPassword(rset.getString("Userpass"));
                user.setNivel(rset.getString("Nivel"));
                user.setContacto(rset.getString("Contacto"));
                user.setFechaven(rset.getDate("FechaVen"));
                user.setIdentint(Integer.valueOf(rset.getString("Idenint")));
            }
        }
        catch (Exception e)
        {
            System.err.println("UserDAO.retrieveUser() Error:\n" + e.getMessage());
            throw new Exception("UserDAO.retrieveUser() Error:\n" + e.getMessage());
        }
        finally
        {
            try
            {
                if (rset != null)
                {
                    rset.close();
                }
                if (pstmt != null)
                {
                    pstmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception ex)
            {
            }
        }
        return user;
    }

    public void updateUser(UserForm user) throws Exception
    {
        PreparedStatement pstmt = null;
        try
        {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("UPDATE usuarios SET Empresa = ?, Userid = ?, Userpass = ?, Nivel = ?, Contacto = ?, FechaVen = ? WHERE Idenint = ?");
            pstmt.setString(1, user.getEmpresa());
            pstmt.setString(2, user.getUserid());
            pstmt.setString(3, user.getUserpass());
            pstmt.setString(4, user.getNivel());
            pstmt.setString(5, user.getContacto());
            pstmt.setString(6, user.getFechaVen());
            pstmt.setString(7, user.getId());
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
        }
        catch (Exception e)
        {
            conn.rollback();
            System.err.println("UserDAO.updateUser() Error:\n" + e.getMessage());
            throw new Exception("UserDAO.updateUser() Error:\n" + e.getMessage());
        }
        finally
        {
            try
            {
                if (pstmt != null)
                {
                    pstmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception ex)
            {
            }
        }
    }

    public void deleteUser(String id) throws Exception
    {
        PreparedStatement pstmt = null;
        try
        {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("DELETE FROM usuarios WHERE Idenint = ?");
            pstmt.setString(1, id);
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
        }
        catch (Exception e)
        {
            conn.rollback();
            System.err.println("UserDAO.deleteUser() Error:\n" + e.getMessage());
            throw new Exception("UserDAO.deleteUser() Error:\n" + e.getMessage());
        }
        finally
        {
            try
            {
                if (pstmt != null)
                {
                    pstmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (Exception ex)
            {
            }
        }
    }

    private Connection conn;
}
